package org.esk.dablog.utils;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.context.ResourceLoaderAware;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.io.IOUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.io.PrintWriter;
import java.util.Date;
import java.util.Arrays;
import java.util.Comparator;

/**
 * This class initializes and upgrades the database schema
 * versioning table is the following:
 * CREATE TABLE VERSIONS(VERSION_ID integer, VERSION integer, DATE_LOADED datetime);
 * where VERSION is number of db version, and DATE_LOADED is the date when the version was updated.
 * User: esk
 * Date: 19.12.2006
 * Time: 17:34:23
 * $Id:$
 */
public class DatabaseInitializer extends JdbcDaoSupport implements ResourceLoaderAware {

    private ResourceLoader resourceLoader;
    private static final String CREATE_VERSION_TABLE = "CREATE TABLE VERSIONS(VERSION integer NOT NULL, DATE_LOADED timestamp)";
    private static final String CREATE_VERSION_TABLE_PK = "ALTER TABLE VERSIONS ADD PRIMARY KEY(VERSION)";
    private static final String INSERT_VERSION = "INSERT INTO VERSIONS (VERSION, DATE_LOADED) VALUES (?,?)";

    public void setResourceLoader(ResourceLoader resourceLoader) {
        this.resourceLoader = resourceLoader;
    }

    protected void initDao() throws Exception {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(resourceLoader);
        Resource[] resources = resolver.getResources("/WEB-INF/db/**");
        if (resources == null || resources.length == 0) {
            return;
        }
        Arrays.sort(resources, new Comparator<Resource>() {
            public int compare(Resource o1, Resource o2) {
                return o1.getFilename().compareTo(o2.getFilename());
            }
        });
        int version = getVersion();
        for (Resource r : resources) {
            String[] parts = StringUtils.split(r.getFilename(), '.');
            int num = NumberUtils.toInt(parts[0]);
            if (num > version) {
                processScript(r);
                getJdbcTemplate().update(INSERT_VERSION, new Object[] {++version, new Date()} );
            }
        }
        super.initDao();    //To change body of overridden methods use File | Settings | File Templates.
    }

    /**
     * Processes sql script
     * @param r
     */
    private void processScript(Resource r) throws Exception {
        String result = IOUtils.toString(r.getInputStream());
        String[] statements = StringUtils.splitByWholeSeparator(result, "\n;");
        for (String statement : statements) {
            String[] lines = StringUtils.split(statement, '\n');
            StringBuffer toExecute = new StringBuffer();
            for (String line : lines) {
                if (!line.startsWith("--")) {
                    toExecute.append(' ').append(line);
                }
            }
            if (!StringUtils.isBlank(toExecute.toString())) {
                try {
                getJdbcTemplate().execute(toExecute.toString());
                } catch(Exception ex) {
                    logger.error("Error executing statement: ", ex);
                }
            }
            logger.debug("statement: " + statement);
        }
    }


    /**
     * returns version of the database;
     * @return
     */
    private int getVersion() {
        int version = 0;
        try {
            version = getJdbcTemplate().queryForInt("select max(version) from versions");
        } catch (Exception ex) {
            //exception means no VERSIONS table exists
            getJdbcTemplate().execute(CREATE_VERSION_TABLE);
            getJdbcTemplate().execute(CREATE_VERSION_TABLE_PK);
        }
        return version;
    }
}
